
package com.craterhill.java.ems.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.craterhill.java.ems.common.beans.Role;
import com.craterhill.java.ems.common.beans.Staff;
import com.craterhill.java.ems.dao.impl.AbstractDao;
import com.craterhill.java.ems.dao.utils.EMSResultSetExtractor;

public interface StaffDao extends EMSDao<Staff> {

	public final String FIELD_LIST = "obj.staff_id, obj.staff_number, obj.position_id, obj.user_name, obj.password, obj.first_name, obj.middle_name, obj.last_name, obj.date_of_birth, obj.gender, obj.street, obj.area, obj.city, obj.state_id, obj.country_id, obj.zip_code, obj.mobile_number, obj.home_phone_number, obj.other_phone_number, obj.email_id, obj.image_path, obj.spouse_name, obj.more_info, obj.qualification, obj.university, obj.experience, obj.subject_matter_expert, obj.additional_curricular_activities, obj.rewards_or_achievements, obj.memberships, obj.others, obj.date_of_hire, obj.date_of_termination, obj.date_of_retirement, obj.can_change_password"
			+ AbstractDao.MONITER_FIELDS;

	public final String SAVE_STAFF = "insert into staff (staff_number, position_id, user_name, password, first_name, middle_name, last_name, date_of_birth, gender, street, area, city, state_id, country_id, zip_code, mobile_number, home_phone_number, other_phone_number, email_id, image_path, spouse_name, more_info, qualification, university, experience, subject_matter_expert, additional_curricular_activities, rewards_or_achievements, memberships, others, date_of_hire, date_of_termination, date_of_retirement, can_change_password, created_by) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	public final String UPDATE_STAFF = "update staff set staff_number = ?, position_id = ?, user_name = ?, password = ?, first_name = ?, middle_name = ?, last_name = ?, date_of_birth = ?, gender = ?, street = ?, area = ?, city = ?, state_id = ?, country_id = ?, zip_code = ?, mobile_number = ?, home_phone_number = ?, other_phone_number = ?, email_id = ?, image_path = ?, spouse_name = ?, more_info = ?, qualification = ?, university = ?, experience = ?, subject_matter_expert = ?, additional_curricular_activities = ?, rewards_or_achievements = ?, memberships = ?, others = ?, date_of_hire = ?, date_of_termination = ?, date_of_retirement = ?, can_change_password = ?, status = ?, modified_date = ? where staff_id = ?";
	public final String DELETE_STAFF = "delete from staff where staff_id = ?";

	public final String FIND_ALL_STAFF = "select " + FIELD_LIST + " from staff obj";
	public final String FIND_STAFF_BY_ID = FIND_ALL_STAFF + " where staff_id = ?";
	public final String FIND_STAFF_BY_USERNAME_PASSWORD = FIND_ALL_STAFF + " where user_name = ? and password = ?";
	public final String FIND_STAFF_BY_ROLE_ID = "select " + FIELD_LIST
			+ " from staff obj,staff_roles staffRoles where staffRoles.role_id = ? and obj.staff_id = staffRoles.staff_id";
	public final String FIND_STAFF_AUTHORITIES = "select role.role_id,role.role_name,role.role_short_name from staff_roles staffRoles, roles role where staffRoles.role_id = role.role_id and staffRoles.staff_id = ?";

	public Staff findByUsernameAndPassword(final String username, final String password);

	public List<Staff> findByRole(final int roleId);

	public List<Role> findStaffAuthorities(final int staffId);

	public final class StaffExtractor extends EMSResultSetExtractor<Staff> {

		@Override
		protected Staff extractResultSetData(ResultSet resultSet) throws SQLException {
			Staff staff = new Staff();
			staff.setStaffId(resultSet.getInt(1));
			staff.setStaffNumber(resultSet.getString(2));
			staff.setPositionId(resultSet.getInt(3));
			staff.setUserName(resultSet.getString(4));
			staff.setPassword(resultSet.getString(5));
			staff.setFirstName(resultSet.getString(6));
			staff.setMiddleName(resultSet.getString(7));
			staff.setLastName(resultSet.getString(8));
			staff.setDateOfBirth(resultSet.getDate(9));
			staff.setGender(resultSet.getBoolean(10));
			staff.setStreet(resultSet.getString(11));
			staff.setArea(resultSet.getString(12));
			staff.setCity(resultSet.getString(13));
			staff.setStateId(resultSet.getInt(14));
			staff.setCountryId(resultSet.getInt(15));
			staff.setZipCode(resultSet.getInt(16));
			staff.setMobileNumber(resultSet.getString(17));
			staff.setHomePhoneNumber(resultSet.getString(18));
			staff.setOtherPhoneNumber(resultSet.getString(19));
			staff.setEmailId(resultSet.getString(20));
			staff.setImagePath(resultSet.getString(21));
			staff.setSpouseName(resultSet.getString(22));
			staff.setMoreInfo(resultSet.getString(23));
			staff.setQualification(resultSet.getString(24));
			staff.setUniversity(resultSet.getString(25));
			staff.setExperience(resultSet.getString(26));
			staff.setSubjectMatterExpert(resultSet.getString(27));
			staff.setAdditionalCurricularActivities(resultSet.getString(28));
			staff.setRewardsOrAchievements(resultSet.getString(29));
			staff.setMemberships(resultSet.getString(30));
			staff.setOthers(resultSet.getString(31));
			staff.setDateOfHire(resultSet.getDate(32));
			staff.setDateOfTermination(resultSet.getDate(33));
			staff.setDateOfRetirement(resultSet.getDate(34));
			staff.setCanChangePassword(resultSet.getBoolean(35));
			staff.setCreatedBy(resultSet.getInt(36));
			staff.setStatus(resultSet.getBoolean(37));
			staff.setCreatedDate(resultSet.getDate(38));
			staff.setModifiedDate(resultSet.getDate(39));
			return staff;
		}
	}
}
